package com.wfs.myapp.repository.bk;

import com.wfs.myapp.entity.domain.bk.BkBook;
import com.wfs.myapp.entity.vo.SearchVo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface BookRepository extends JpaRepository<BkBook, Long> {


    List<BkBook> findAllByUserId(Long userId);


    @Query(nativeQuery = true, value = " SELECT * FROM bk_book b WHERE b.user_id = :userId limit :page,:size ")
    List<BkBook> findByUserId(Long userId, Integer page, Integer size);
    @Query(nativeQuery = true, value = " SELECT COUNT(*) FROM bk_book b WHERE b.user_id = ?1")
    long countByUserId(Long userId);

    @Query(nativeQuery = true, value = " SELECT count(*) FROM bk_book b " +
            " WHERE b.user_id = :userId " +
            " AND b.id not in ( " +
            " select o.book_id from bk_order o where o.master_user_id = :userId and o.status in (1, 3, 4) " +
            " ) ")
    long countPendingBookByUserId(Long userId);
    @Query(nativeQuery = true, value = " SELECT * FROM bk_book b " +
            " WHERE b.user_id = :userId " +
            " AND b.id not in ( " +
            " select o.book_id from bk_order o where o.master_user_id = :userId and o.status in (1, 3, 4) " +
            " ) " +
            " limit :page,:size ")
    List<BkBook> findPendingBookByUserId(Long userId, Integer page, Integer size);

    @Query(nativeQuery = true, value =
            " SELECT 2 as type, b.id, b.book_name name, b.book_avatar image, b.description, b.author, u.nickname userName, u.id userId, b.status " +
                    "FROM bk_book b " +
                    "LEFT JOIN user u ON b.user_id = u.id " +
                    "WHERE b.book_name like CONCAT('%',:name,'%') "
            )
    List<SearchVo.SearchItem> findBySearchName(String name);

    @Query(nativeQuery = true, value = " " +
            " SELECT 1 as type, u.id, u.nickname name, u.avatar image, '' description, '' author, '' userName, u.id userId, u.status FROM user u WHERE u.nickname like CONCAT('%',:name,'%') " +
            " UNION ALL " +
            " SELECT 2 as type, b.id, b.book_name name, b.book_avatar image, b.description, b.author, u.nickname userName, u.id userId, b.status FROM bk_book b LEFT JOIN user u ON b.user_id = u.id WHERE b.book_name like CONCAT('%',:name,'%') " +
            " UNION ALL " +
            " SELECT 3 as type, g.id, g.group_name name, g.group_avatar image, g.description, '' author, '' userName, u.id userId, 1 status FROM bk_group g LEFT JOIN user u ON g.master_user_id = u.id WHERE g.group_name like CONCAT('%',:name,'%') "
    )
    List<SearchVo.SearchItem> findAllBySearchName(String name);

    @Query(nativeQuery = true, value = " SELECT 2 type, b.id, b.book_name name, b.book_avatar image, b.author author " +
            " FROM bk_book b " +
            " ORDER BY b.created_time desc " +
            " limit :page,:size ")
    List<SearchVo.SearchItem> findAllByTime(Integer page, Integer size);
}

